First or Last

Use the First or Last tool to extract the first, last, or a specific value from a transaction table. For example, the first transaction date or sales channel of first purchase.

With the addition of filters you can extend the tool to calculate values such as "sales channel of first purchase of product X" which can help you to establish trends in how prospects become customers. When running campaigns using Campaign Manager, you can also use the tool to generate Campaign Events by establishing whether a customer's first purchase of a promoted product occurred within a specified period of time after the campaign.

Procedure

1. Drag the tool to the Workspace.
2. Choose the join operator.
3. Click the icon to the right of the New First Or Last field.
4. In the Display Name field, enter a name for the field.

If you save the tool as a template, this name will be used to display it in the Templates tab. The Display Name is also used in the Caption area if you insert the tool into a document.

5. If you want the new column to be automatically indexed, select the Index Column check box. You should only index columns when necessary. For example, when engineered columns have > 250 discrete values, indexing can start to improve query performance.

6. Optional setting: if you want to make the field permanent so that it can be viewed and selected in the Data Explorer, click the Optional expand icon. In the Table Column Name field, enter the name you want to use to display the column in the Data Explorer tab.
7. Populate the remaining Settings fields:
   • Source Column: This is the column that will be copied from the foreign Transaction Table to the specified Target Table, i.e. it will provide the value for the new column. Depending on what you are trying it achieve, it could be something like Sales Channel, Quarter, or could simply be the Transaction Date when you just wanted to extract First Transaction Date or each of your customers.
   • Target Table: This is the table that the new column will be created on. Once you have selected a Source Column, only primary tables will be available to select, that is on the One side of the One to Many join.
   • Sort Column: This is the transaction column that the sort will be based on, and which will be used to extract the corresponding Source Column. In a transaction table it would usually be a Transaction Date or Order Date. This column must either exist on the same table as the specified Source Column, or a linked primary table.
   • Sequence Number: This tells the tool which transaction value to extract. A sequence number of 0 will select the first or last (depending on the 'From Beginning' check box) value from the selected Sort Column. Incrementing the sequence number will select the next value, and so on. So if the Sort Column is [Order].[Date] and the sequence number is 1, then it will select the second last transaction, and so on. The higher you increment this number, the larger the 'Null' column segment will grow as you exclude customers who have only made a small number of transactions.
   • From Beginning: Checking this will reverse the default selection order and select the Oldest transaction.
   • Filter: This applies standard Campaign Manager filtering functionality to extract specific subsets of values, according to Product, Date , or Geographic criteria.

Examples

If you are looking to establish what time of year particular products sold best, and when prospects were likely to drop into a particular sales channel, then it might be useful to calculate the Quarter that existing customers made a first purchase of a particular product. In this case the Source Column would be Quarter, the new column would be created on the Customer table, and you would sort by Order Date. You could then filter by Product Code to refine your segment.

If you are looking to take a quick snapshot of the purchase behavior of your most profitable customers, you could quickly calculate how many units were included in their most recent order as follows:

• Data Engineering
  • Data Engineering Tools Overview
  • Aggregate Functions
  • Aggregate
  • Auto Numeric Band
  • Copy Values To
  • Days Between
  • Decode
  • Engine Cleanup
  • Expression
  • Engine Expressions
  • First or Last
  • Make Permanent
  • Numeric Band
  • Parametric Groups
  • Percentile
  • Prioritize List
  • Quantile Band
  • Rank By X